SET client_min_messages TO 'warning';
DROP ROLE IF EXISTS regress_priv_group1;
DROP ROLE IF EXISTS regress_priv_group2;
DROP ROLE IF EXISTS regress_priv_user1;
DROP ROLE IF EXISTS regress_priv_user2;
DROP ROLE IF EXISTS regress_priv_user3;
DROP ROLE IF EXISTS regress_priv_user4;
DROP ROLE IF EXISTS regress_priv_user5;
DROP ROLE IF EXISTS regress_priv_user6;
SELECT lo_unlink(oid) FROM pg_largeobject_metadata WHERE oid >= 1000 AND oid < 3000 ORDER BY oid;
RESET client_min_messages;
CREATE USER regress_priv_user1;
CREATE USER regress_priv_user2;
CREATE USER regress_priv_user3;
CREATE USER regress_priv_user4;
CREATE USER regress_priv_user5;
CREATE USER regress_priv_user5;
	CREATE GROUP regress_priv_group1;
	CREATE GROUP regress_priv_group1;
CREATE GROUP regress_priv_group2 WITH USER regress_priv_user1, regress_priv_user2;
ALTER GROUP regress_priv_group1 ADD USER regress_priv_user4;
ALTER GROUP regress_priv_group2 ADD USER regress_priv_user2;
	ALTER GROUP regress_priv_group2 DROP USER regress_priv_user2;
	ALTER GROUP regress_priv_group2 DROP USER regress_priv_user2;
GRANT regress_priv_group2 TO regress_priv_user4 WITH ADMIN OPTION;
SET SESSION AUTHORIZATION regress_priv_user1;
SELECT session_user, current_user;
CREATE TABLE atest1 ( a int, b text );
SELECT * FROM atest1;
INSERT INTO atest1 VALUES (1, 'one');
DELETE FROM atest1;
UPDATE atest1 SET a = 1 WHERE b = 'blech';
TRUNCATE atest1;
BEGIN;
LOCK atest1 IN ACCESS EXCLUSIVE MODE;
COMMIT;
REVOKE ALL ON atest1 FROM PUBLIC;
SELECT * FROM atest1;
GRANT ALL ON atest1 TO regress_priv_user2;
GRANT SELECT ON atest1 TO regress_priv_user3, regress_priv_user4;
SELECT * FROM atest1;
CREATE TABLE atest2 (col1 varchar(10), col2 boolean);
GRANT SELECT ON atest2 TO regress_priv_user2;
GRANT UPDATE ON atest2 TO regress_priv_user3;
GRANT INSERT ON atest2 TO regress_priv_user4;
GRANT TRUNCATE ON atest2 TO regress_priv_user5;
SET SESSION AUTHORIZATION regress_priv_user2;
SELECT session_user, current_user;
SELECT * FROM atest1;
 SELECT * FROM atest2;
 INSERT INTO atest1 VALUES (2, 'two');
 INSERT INTO atest2 VALUES ('foo', true);
 INSERT INTO atest1 SELECT 1, b FROM atest1;
 UPDATE atest1 SET a = 1 WHERE a = 2;
 UPDATE atest2 SET col2 = NOT col2;
 SELECT * FROM atest1 FOR UPDATE;
 SELECT * FROM atest2 FOR UPDATE;
 DELETE FROM atest2;
 TRUNCATE atest2;
 BEGIN;
 BEGIN;
LOCK atest2 IN ACCESS EXCLUSIVE MODE;
 COMMIT;
 COMMIT;
COPY atest2 FROM stdin;
 GRANT ALL ON atest1 TO PUBLIC;
 SELECT * FROM atest1 WHERE ( b IN ( SELECT col1 FROM atest2 ) );
 SELECT * FROM atest1 WHERE ( b IN ( SELECT col1 FROM atest2 ) );
SELECT * FROM atest2 WHERE ( col1 IN ( SELECT b FROM atest1 ) );
SET SESSION AUTHORIZATION regress_priv_user3;
SELECT session_user, current_user;
SELECT * FROM atest1;
 SELECT * FROM atest2;
 INSERT INTO atest1 VALUES (2, 'two');
 INSERT INTO atest2 VALUES ('foo', true);
 INSERT INTO atest1 SELECT 1, b FROM atest1;
 UPDATE atest1 SET a = 1 WHERE a = 2;
 UPDATE atest2 SET col2 = NULL;
 UPDATE atest2 SET col2 = NOT col2;
 UPDATE atest2 SET col2 = true FROM atest1 WHERE atest1.a = 5;
 SELECT * FROM atest1 FOR UPDATE;
 SELECT * FROM atest2 FOR UPDATE;
 DELETE FROM atest2;
 TRUNCATE atest2;
 BEGIN;
 BEGIN;
LOCK atest2 IN ACCESS EXCLUSIVE MODE;
 COMMIT;
 COMMIT;
COPY atest2 FROM stdin;
 SELECT * FROM atest1 WHERE ( b IN ( SELECT col1 FROM atest2 ) );
 SELECT * FROM atest1 WHERE ( b IN ( SELECT col1 FROM atest2 ) );
SELECT * FROM atest2 WHERE ( col1 IN ( SELECT b FROM atest1 ) );
SET SESSION AUTHORIZATION regress_priv_user4;
COPY atest2 FROM stdin;
 bar	true\.SELECT * FROM atest1;
 SET SESSION AUTHORIZATION regress_priv_user1;
 SET SESSION AUTHORIZATION regress_priv_user1;
CREATE TABLE atest12 as  SELECT x AS a, 10001 - x AS b FROM generate_series(1,10000) x;
CREATE INDEX ON atest12 (a);
CREATE INDEX ON atest12 (abs(a));
ALTER TABLE atest12 SET (autovacuum_enabled = off);
SET default_statistics_target = 10000;
VACUUM ANALYZE atest12;
RESET default_statistics_target;
CREATE FUNCTION leak(integer,integer) RETURNS boolean  AS begin return  1 <  2;
 end  LANGUAGE plpgsql immutable;
 end  LANGUAGE plpgsql immutable;
CREATE OPERATOR <<< (procedure = leak, leftarg = integer, rightarg = integer,                     restrict = scalarltsel);
CREATE VIEW atest12v AS  SELECT * FROM atest12 WHERE b <<< 5;
CREATE VIEW atest12sbv WITH (security_barrier=true) AS  SELECT * FROM atest12 WHERE b <<< 5;
GRANT SELECT ON atest12v TO PUBLIC;
GRANT SELECT ON atest12sbv TO PUBLIC;
EXPLAIN (COSTS OFF) SELECT * FROM atest12v x, atest12v y WHERE x.a = y.b;
EXPLAIN (COSTS OFF) SELECT * FROM atest12 x, atest12 y  WHERE x.a = y.b and abs(y.a) <<< 5;
EXPLAIN (COSTS OFF) SELECT * FROM atest12sbv x, atest12sbv y WHERE x.a = y.b;
SET SESSION AUTHORIZATION regress_priv_user2;
CREATE FUNCTION leak2(integer,integer) RETURNS boolean  AS begin raise notice 'leak % %',  1,  2;
 return  1 >  2;
 return  1 >  2;
  LANGUAGE plpgsql immutable;
CREATE OPERATOR >>> (procedure = leak2, leftarg = integer, rightarg = integer,                     restrict = scalargtsel);
EXPLAIN (COSTS OFF) SELECT * FROM atest12 WHERE a >>> 0;
EXPLAIN (COSTS OFF) SELECT * FROM atest12v x, atest12v y WHERE x.a = y.b;
EXPLAIN (COSTS OFF) SELECT * FROM atest12sbv x, atest12sbv y WHERE x.a = y.b;
EXPLAIN (COSTS OFF) SELECT * FROM atest12v x, atest12v y  WHERE x.a = y.b and abs(y.a) <<< 5;
EXPLAIN (COSTS OFF) SELECT * FROM atest12sbv x, atest12sbv y  WHERE x.a = y.b and abs(y.a) <<< 5;
SET SESSION AUTHORIZATION regress_priv_user1;
GRANT SELECT (a, b) ON atest12 TO PUBLIC;
SET SESSION AUTHORIZATION regress_priv_user2;
EXPLAIN (COSTS OFF) SELECT * FROM atest12v x, atest12v y WHERE x.a = y.b;
EXPLAIN (COSTS OFF) SELECT * FROM atest12 x, atest12 y  WHERE x.a = y.b and abs(y.a) <<< 5;
DROP FUNCTION leak2(integer, integer) CASCADE;
SET SESSION AUTHORIZATION regress_priv_user3;
CREATE TABLE atest3 (one int, two int, three int);
GRANT DELETE ON atest3 TO GROUP regress_priv_group2;
SET SESSION AUTHORIZATION regress_priv_user1;
SELECT * FROM atest3;
 DELETE FROM atest3;
 BEGIN;
 BEGIN;
RESET SESSION AUTHORIZATION;
ALTER ROLE regress_priv_user1 NOINHERIT;
SET SESSION AUTHORIZATION regress_priv_user1;
DELETE FROM atest3;
ROLLBACK;
SET SESSION AUTHORIZATION regress_priv_user3;
CREATE VIEW atestv1 AS SELECT * FROM atest1;
 CREATE VIEW atestv2 AS SELECT * FROM atest2;
 CREATE VIEW atestv2 AS SELECT * FROM atest2;
CREATE VIEW atestv3 AS SELECT * FROM atest3;
 CREATE VIEW atestv0 AS SELECT 0 as x WHERE false;
 SELECT * FROM atestv1;
 SELECT * FROM atestv2;
 GRANT SELECT ON atestv1, atestv3 TO regress_priv_user4;
 GRANT SELECT ON atestv1, atestv3 TO regress_priv_user4;
GRANT SELECT ON atestv2 TO regress_priv_user2;
SET SESSION AUTHORIZATION regress_priv_user4;
SELECT * FROM atestv1;
 SELECT * FROM atestv2;
 SELECT * FROM atestv3;
 SELECT * FROM atestv0;
 select * from  ((select a.q1 as x from int8_tbl a offset 0)   union all   (select b.q2 as x from int8_tbl b offset 0)) sswhere false;
 select * from  ((select a.q1 as x from int8_tbl a offset 0)   union all   (select b.q2 as x from int8_tbl b offset 0)) sswhere false;
set constraint_exclusion = on;
select * from  ((select a.q1 as x, random() from int8_tbl a where q1 > 0)   union all   (select b.q2 as x, random() from int8_tbl b where q2 > 0)) sswhere x < 0;
reset constraint_exclusion;
CREATE VIEW atestv4 AS SELECT * FROM atestv3;
 SELECT * FROM atestv4;
 GRANT SELECT ON atestv4 TO regress_priv_user2;
 GRANT SELECT ON atestv4 TO regress_priv_user2;
SET SESSION AUTHORIZATION regress_priv_user2;
SELECT * FROM atestv3;
 SELECT * FROM atestv4;
 SELECT * FROM atest2;
 SELECT * FROM atestv2;
 SET SESSION AUTHORIZATION regress_priv_user1;
 SET SESSION AUTHORIZATION regress_priv_user1;
CREATE TABLE atest5 (one int, two int unique, three int, four int unique);
CREATE TABLE atest6 (one int, two int, blue int);
GRANT SELECT (one), INSERT (two), UPDATE (three) ON atest5 TO regress_priv_user4;
GRANT ALL (one) ON atest5 TO regress_priv_user3;
INSERT INTO atest5 VALUES (1,2,3);
SET SESSION AUTHORIZATION regress_priv_user4;
SELECT * FROM atest5;
 SELECT one FROM atest5;
 COPY atest5 (one) TO stdout;
 SELECT two FROM atest5;
 COPY atest5 (two) TO stdout;
 SELECT atest5 FROM atest5;
 COPY atest5 (one,two) TO stdout;
 SELECT 1 FROM atest5;
 SELECT 1 FROM atest5 a JOIN atest5 b USING (one);
 SELECT 1 FROM atest5 a JOIN atest5 b USING (two);
 SELECT 1 FROM atest5 a NATURAL JOIN atest5 b;
 SELECT * FROM (atest5 a JOIN atest5 b USING (one)) j;
 SELECT j.* FROM (atest5 a JOIN atest5 b USING (one)) j;
 SELECT (j.*) IS NULL FROM (atest5 a JOIN atest5 b USING (one)) j;
 SELECT one FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)) j;
 SELECT j.one FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)) j;
 SELECT two FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)) j;
 SELECT j.two FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)) j;
 SELECT y FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)) j;
 SELECT j.y FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)) j;
 SELECT * FROM (atest5 a JOIN atest5 b USING (one));
 SELECT a.* FROM (atest5 a JOIN atest5 b USING (one));
 SELECT (a.*) IS NULL FROM (atest5 a JOIN atest5 b USING (one));
 SELECT two FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one));
 SELECT a.two FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one));
 SELECT y FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one));
 SELECT b.y FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one));
 SELECT y FROM (atest5 a LEFT JOIN atest5 b(one,x,y,z) USING (one));
 SELECT b.y FROM (atest5 a LEFT JOIN atest5 b(one,x,y,z) USING (one));
 SELECT y FROM (atest5 a FULL JOIN atest5 b(one,x,y,z) USING (one));
 SELECT b.y FROM (atest5 a FULL JOIN atest5 b(one,x,y,z) USING (one));
 SELECT 1 FROM atest5 WHERE two = 2;
 SELECT * FROM atest1, atest5;
 SELECT atest1.* FROM atest1, atest5;
 SELECT atest1.*,atest5.one FROM atest1, atest5;
 SELECT atest1.*,atest5.one FROM atest1 JOIN atest5 ON (atest1.a = atest5.two);
 SELECT atest1.*,atest5.one FROM atest1 JOIN atest5 ON (atest1.a = atest5.one);
 SELECT one, two FROM atest5;
 SET SESSION AUTHORIZATION regress_priv_user1;
 SET SESSION AUTHORIZATION regress_priv_user1;
GRANT SELECT (one,two) ON atest6 TO regress_priv_user4;
SET SESSION AUTHORIZATION regress_priv_user4;
SELECT one, two FROM atest5 NATURAL JOIN atest6;
 SET SESSION AUTHORIZATION regress_priv_user1;
 SET SESSION AUTHORIZATION regress_priv_user1;
GRANT SELECT (two) ON atest5 TO regress_priv_user4;
SET SESSION AUTHORIZATION regress_priv_user4;
SELECT one, two FROM atest5 NATURAL JOIN atest6;
 INSERT INTO atest5 (two) VALUES (3);
 COPY atest5 FROM stdin;
 COPY atest5 (two) FROM stdin;
 1\.INSERT INTO atest5 (three) VALUES (4);
 INSERT INTO atest5 VALUES (5,5,5);
 UPDATE atest5 SET three = 10;
 UPDATE atest5 SET one = 8;
 UPDATE atest5 SET three = 5, one = 2;
 INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = 10;
 INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = 10;
INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = 10 RETURNING atest5.three;
INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = 10 RETURNING atest5.one;
INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = EXCLUDED.one;
INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = EXCLUDED.three;
INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set one = 8;
 INSERT INTO atest5(three) VALUES (4) ON CONFLICT (two) DO UPDATE set three = 10;
 INSERT INTO atest5(four) VALUES (4);
 SET SESSION AUTHORIZATION regress_priv_user1;
 SET SESSION AUTHORIZATION regress_priv_user1;
GRANT INSERT (four) ON atest5 TO regress_priv_user4;
SET SESSION AUTHORIZATION regress_priv_user4;
INSERT INTO atest5(four) VALUES (4) ON CONFLICT (four) DO UPDATE set three = 3;
 INSERT INTO atest5(four) VALUES (4) ON CONFLICT ON CONSTRAINT atest5_four_key DO UPDATE set three = 3;
 INSERT INTO atest5(four) VALUES (4);
 SET SESSION AUTHORIZATION regress_priv_user1;
 SET SESSION AUTHORIZATION regress_priv_user1;
GRANT SELECT (four) ON atest5 TO regress_priv_user4;
SET SESSION AUTHORIZATION regress_priv_user4;
INSERT INTO atest5(four) VALUES (4) ON CONFLICT (four) DO UPDATE set three = 3;
 INSERT INTO atest5(four) VALUES (4) ON CONFLICT ON CONSTRAINT atest5_four_key DO UPDATE set three = 3;
 SET SESSION AUTHORIZATION regress_priv_user1;
 SET SESSION AUTHORIZATION regress_priv_user1;
REVOKE ALL (one) ON atest5 FROM regress_priv_user4;
GRANT SELECT (one,two,blue) ON atest6 TO regress_priv_user4;
SET SESSION AUTHORIZATION regress_priv_user4;
SELECT one FROM atest5;
 UPDATE atest5 SET one = 1;
 SELECT atest6 FROM atest6;
 COPY atest6 TO stdout;
 SET SESSION AUTHORIZATION regress_priv_user1;
 SET SESSION AUTHORIZATION regress_priv_user1;
CREATE TABLE t1 (c1 int, c2 int, c3 int check (c3 < 5), primary key (c1, c2));
GRANT SELECT (c1) ON t1 TO regress_priv_user2;
GRANT INSERT (c1, c2, c3) ON t1 TO regress_priv_user2;
GRANT UPDATE (c1, c2, c3) ON t1 TO regress_priv_user2;
INSERT INTO t1 VALUES (1, 1, 1);
INSERT INTO t1 VALUES (1, 2, 1);
INSERT INTO t1 VALUES (2, 1, 2);
INSERT INTO t1 VALUES (2, 2, 2);
INSERT INTO t1 VALUES (3, 1, 3);
SET SESSION AUTHORIZATION regress_priv_user2;
INSERT INTO t1 (c1, c2) VALUES (1, 1);
 UPDATE t1 SET c2 = 1;
 INSERT INTO t1 (c1, c2) VALUES (null, null);
 INSERT INTO t1 (c3) VALUES (null);
 INSERT INTO t1 (c1) VALUES (5);
 UPDATE t1 SET c3 = 10;
 SET SESSION AUTHORIZATION regress_priv_user1;
 SET SESSION AUTHORIZATION regress_priv_user1;
DROP TABLE t1;
CREATE TABLE errtst(a text, b text NOT NULL, c text, secret1 text, secret2 text) PARTITION BY LIST (a);
CREATE TABLE errtst_part_1(secret2 text, c text, a text, b text NOT NULL, secret1 text);
CREATE TABLE errtst_part_2(secret1 text, secret2 text, a text, c text, b text NOT NULL);
ALTER TABLE errtst ATTACH PARTITION errtst_part_1 FOR VALUES IN ('aaa');
ALTER TABLE errtst ATTACH PARTITION errtst_part_2 FOR VALUES IN ('aaaa');
GRANT SELECT (a, b, c) ON TABLE errtst TO regress_priv_user2;
GRANT UPDATE (a, b, c) ON TABLE errtst TO regress_priv_user2;
GRANT INSERT (a, b, c) ON TABLE errtst TO regress_priv_user2;
INSERT INTO errtst_part_1 (a, b, c, secret1, secret2)VALUES ('aaa', 'bbb', 'ccc', 'the body', 'is in the attic');
SET SESSION AUTHORIZATION regress_priv_user2;
INSERT INTO errtst (a, b) VALUES ('aaa', NULL);
UPDATE errtst SET b = NULL;
UPDATE errtst SET a = 'aaa', b = NULL;
UPDATE errtst SET a = 'aaaa', b = NULL;
UPDATE errtst SET a = 'aaaa', b = NULL WHERE a = 'aaa';
SET SESSION AUTHORIZATION regress_priv_user1;
DROP TABLE errtst;
SET SESSION AUTHORIZATION regress_priv_user1;
ALTER TABLE atest6 ADD COLUMN three integer;
GRANT DELETE ON atest5 TO regress_priv_user3;
GRANT SELECT (two) ON atest5 TO regress_priv_user3;
REVOKE ALL (one) ON atest5 FROM regress_priv_user3;
GRANT SELECT (one) ON atest5 TO regress_priv_user4;
SET SESSION AUTHORIZATION regress_priv_user4;
SELECT atest6 FROM atest6;
 SELECT one FROM atest5 NATURAL JOIN atest6;
 SET SESSION AUTHORIZATION regress_priv_user1;
 SET SESSION AUTHORIZATION regress_priv_user1;
ALTER TABLE atest6 DROP COLUMN three;
SET SESSION AUTHORIZATION regress_priv_user4;
SELECT atest6 FROM atest6;
 SELECT one FROM atest5 NATURAL JOIN atest6;
 SET SESSION AUTHORIZATION regress_priv_user1;
 SET SESSION AUTHORIZATION regress_priv_user1;
ALTER TABLE atest6 DROP COLUMN two;
REVOKE SELECT (one,blue) ON atest6 FROM regress_priv_user4;
SET SESSION AUTHORIZATION regress_priv_user4;
SELECT * FROM atest6;
 SELECT 1 FROM atest6;
 SET SESSION AUTHORIZATION regress_priv_user3;
 SET SESSION AUTHORIZATION regress_priv_user3;
DELETE FROM atest5 WHERE one = 1;
 DELETE FROM atest5 WHERE two = 2;
 SET SESSION AUTHORIZATION regress_priv_user1;
 SET SESSION AUTHORIZATION regress_priv_user1;
CREATE TABLE atestp1 (f1 int, f2 int);
CREATE TABLE atestp2 (fx int, fy int);
CREATE TABLE atestc (fz int) INHERITS (atestp1, atestp2);
GRANT SELECT(fx,fy,tableoid) ON atestp2 TO regress_priv_user2;
GRANT SELECT(fx) ON atestc TO regress_priv_user2;
SET SESSION AUTHORIZATION regress_priv_user2;
SELECT fx FROM atestp2;
 SELECT fy FROM atestp2;
 SELECT atestp2 FROM atestp2;
 SELECT tableoid FROM atestp2;
 SELECT fy FROM atestc;
 SET SESSION AUTHORIZATION regress_priv_user1;
 SET SESSION AUTHORIZATION regress_priv_user1;
GRANT SELECT(fy,tableoid) ON atestc TO regress_priv_user2;
SET SESSION AUTHORIZATION regress_priv_user2;
SELECT fx FROM atestp2;
 SELECT fy FROM atestp2;
 SELECT atestp2 FROM atestp2;
 SELECT tableoid FROM atestp2;
 SET SESSION AUTHORIZATION regress_priv_user1;
 SET SESSION AUTHORIZATION regress_priv_user1;
REVOKE ALL ON atestc FROM regress_priv_user2;
GRANT ALL ON atestp1 TO regress_priv_user2;
SET SESSION AUTHORIZATION regress_priv_user2;
SELECT f2 FROM atestp1;
 SELECT f2 FROM atestc;
 DELETE FROM atestp1;
 DELETE FROM atestc;
 UPDATE atestp1 SET f1 = 1;
 UPDATE atestc SET f1 = 1;
 TRUNCATE atestp1;
 TRUNCATE atestc;
 BEGIN;
 BEGIN;
LOCK atestp1;
END;
BEGIN;
LOCK atestc;
END;
\c -REVOKE ALL PRIVILEGES ON LANGUAGE sql FROM PUBLIC;
GRANT USAGE ON LANGUAGE sql TO regress_priv_user1;
 GRANT USAGE ON LANGUAGE c TO PUBLIC;
 SET SESSION AUTHORIZATION regress_priv_user1;
 SET SESSION AUTHORIZATION regress_priv_user1;
GRANT USAGE ON LANGUAGE sql TO regress_priv_user2;
 CREATE FUNCTION priv_testfunc1(int) RETURNS int AS 'select 2 *  1;
 CREATE FUNCTION priv_testfunc1(int) RETURNS int AS 'select 2 *  1;
' LANGUAGE sql;
' LANGUAGE sql;
CREATE FUNCTION priv_testfunc2(int) RETURNS int AS 'select 3 *  1;
' LANGUAGE sql;
' LANGUAGE sql;
CREATE AGGREGATE priv_testagg1(int) (sfunc = int4pl, stype = int4);
CREATE PROCEDURE priv_testproc1(int) AS 'select  1;
' LANGUAGE sql;
' LANGUAGE sql;
REVOKE ALL ON FUNCTION priv_testfunc1(int), priv_testfunc2(int), priv_testagg1(int) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION priv_testfunc1(int), priv_testfunc2(int), priv_testagg1(int) TO regress_priv_user2;
REVOKE ALL ON FUNCTION priv_testproc1(int) FROM PUBLIC;
 REVOKE ALL ON PROCEDURE priv_testproc1(int) FROM PUBLIC;
 REVOKE ALL ON PROCEDURE priv_testproc1(int) FROM PUBLIC;
GRANT EXECUTE ON PROCEDURE priv_testproc1(int) TO regress_priv_user2;
GRANT USAGE ON FUNCTION priv_testfunc1(int) TO regress_priv_user3;
 GRANT USAGE ON FUNCTION priv_testagg1(int) TO regress_priv_user3;
 GRANT USAGE ON PROCEDURE priv_testproc1(int) TO regress_priv_user3;
 GRANT ALL PRIVILEGES ON FUNCTION priv_testfunc1(int) TO regress_priv_user4;
 GRANT ALL PRIVILEGES ON FUNCTION priv_testfunc1(int) TO regress_priv_user4;
GRANT ALL PRIVILEGES ON FUNCTION priv_testfunc_nosuch(int) TO regress_priv_user4;
GRANT ALL PRIVILEGES ON FUNCTION priv_testagg1(int) TO regress_priv_user4;
GRANT ALL PRIVILEGES ON PROCEDURE priv_testproc1(int) TO regress_priv_user4;
CREATE FUNCTION priv_testfunc4(boolean) RETURNS text  AS 'select col1 from atest2 where col2 =  1;
'  LANGUAGE sql SECURITY DEFINER;
'  LANGUAGE sql SECURITY DEFINER;
GRANT EXECUTE ON FUNCTION priv_testfunc4(boolean) TO regress_priv_user3;
SET SESSION AUTHORIZATION regress_priv_user2;
SELECT priv_testfunc1(5), priv_testfunc2(5);
 CREATE FUNCTION priv_testfunc3(int) RETURNS int AS 'select 2 *  1;
 CREATE FUNCTION priv_testfunc3(int) RETURNS int AS 'select 2 *  1;
' LANGUAGE sql;
' LANGUAGE sql;
SELECT priv_testagg1(x) FROM (VALUES (1), (2), (3)) _(x);
 CALL priv_testproc1(6);
 SET SESSION AUTHORIZATION regress_priv_user3;
 SET SESSION AUTHORIZATION regress_priv_user3;
SELECT priv_testfunc1(5);
 SELECT priv_testagg1(x) FROM (VALUES (1), (2), (3)) _(x);
 CALL priv_testproc1(6);
 SELECT col1 FROM atest2 WHERE col2 = true;
 SELECT priv_testfunc4(true);
 SET SESSION AUTHORIZATION regress_priv_user4;
 SET SESSION AUTHORIZATION regress_priv_user4;
SELECT priv_testfunc1(5);
 SELECT priv_testagg1(x) FROM (VALUES (1), (2), (3)) _(x);
 CALL priv_testproc1(6);
 DROP FUNCTION priv_testfunc1(int);
 DROP AGGREGATE priv_testagg1(int);
 DROP PROCEDURE priv_testproc1(int);
 \c -DROP FUNCTION priv_testfunc1(int);
 GRANT ALL PRIVILEGES ON LANGUAGE sql TO PUBLIC;
 GRANT ALL PRIVILEGES ON LANGUAGE sql TO PUBLIC;
BEGIN;
SELECT '{1}'::int4[]::int8[];
REVOKE ALL ON FUNCTION int8(integer) FROM PUBLIC;
SELECT '{1}'::int4[]::int8[];
 SET SESSION AUTHORIZATION regress_priv_user4;
 SET SESSION AUTHORIZATION regress_priv_user4;
SELECT '{1}'::int4[]::int8[];
 ROLLBACK;
 ROLLBACK;
\c -CREATE TYPE priv_testtype1 AS (a int, b text);
REVOKE USAGE ON TYPE priv_testtype1 FROM PUBLIC;
GRANT USAGE ON TYPE priv_testtype1 TO regress_priv_user2;
GRANT USAGE ON TYPE _priv_testtype1 TO regress_priv_user2;
 GRANT USAGE ON DOMAIN priv_testtype1 TO regress_priv_user2;
 CREATE DOMAIN priv_testdomain1 AS int;
 CREATE DOMAIN priv_testdomain1 AS int;
REVOKE USAGE on DOMAIN priv_testdomain1 FROM PUBLIC;
GRANT USAGE ON DOMAIN priv_testdomain1 TO regress_priv_user2;
GRANT USAGE ON TYPE priv_testdomain1 TO regress_priv_user2;
 SET SESSION AUTHORIZATION regress_priv_user1;
 SET SESSION AUTHORIZATION regress_priv_user1;
CREATE AGGREGATE priv_testagg1a(priv_testdomain1) (sfunc = int4_sum, stype = bigint);
CREATE DOMAIN priv_testdomain2a AS priv_testdomain1;
CREATE DOMAIN priv_testdomain3a AS int;
CREATE FUNCTION castfunc(int) RETURNS priv_testdomain3a AS  SELECT  1::priv_testdomain3a  LANGUAGE SQL;
CREATE CAST (priv_testdomain1 AS priv_testdomain3a) WITH FUNCTION castfunc(int);
DROP FUNCTION castfunc(int) CASCADE;
DROP DOMAIN priv_testdomain3a;
CREATE FUNCTION priv_testfunc5a(a priv_testdomain1) RETURNS int LANGUAGE SQL AS  SELECT  1 ;
CREATE FUNCTION priv_testfunc6a(b int) RETURNS priv_testdomain1 LANGUAGE SQL AS  SELECT  1::priv_testdomain1 ;
CREATE OPERATOR !+! (PROCEDURE = int4pl, LEFTARG = priv_testdomain1, RIGHTARG = priv_testdomain1);
CREATE TABLE test5a (a int, b priv_testdomain1);
CREATE TABLE test6a OF priv_testtype1;
CREATE TABLE test10a (a int[], b priv_testtype1[]);
CREATE TABLE test9a (a int, b int);
ALTER TABLE test9a ADD COLUMN c priv_testdomain1;
ALTER TABLE test9a ALTER COLUMN b TYPE priv_testdomain1;
CREATE TYPE test7a AS (a int, b priv_testdomain1);
CREATE TYPE test8a AS (a int, b int);
ALTER TYPE test8a ADD ATTRIBUTE c priv_testdomain1;
ALTER TYPE test8a ALTER ATTRIBUTE b TYPE priv_testdomain1;
CREATE TABLE test11a AS (SELECT 1::priv_testdomain1 AS a);
REVOKE ALL ON TYPE priv_testtype1 FROM PUBLIC;
SET SESSION AUTHORIZATION regress_priv_user2;
CREATE AGGREGATE priv_testagg1b(priv_testdomain1) (sfunc = int4_sum, stype = bigint);
CREATE DOMAIN priv_testdomain2b AS priv_testdomain1;
CREATE DOMAIN priv_testdomain3b AS int;
CREATE FUNCTION castfunc(int) RETURNS priv_testdomain3b AS  SELECT  1::priv_testdomain3b  LANGUAGE SQL;
CREATE CAST (priv_testdomain1 AS priv_testdomain3b) WITH FUNCTION castfunc(int);
CREATE FUNCTION priv_testfunc5b(a priv_testdomain1) RETURNS int LANGUAGE SQL AS  SELECT  1 ;
CREATE FUNCTION priv_testfunc6b(b int) RETURNS priv_testdomain1 LANGUAGE SQL AS  SELECT  1::priv_testdomain1 ;
CREATE OPERATOR !! (PROCEDURE = priv_testfunc5b, RIGHTARG = priv_testdomain1);
CREATE TABLE test5b (a int, b priv_testdomain1);
CREATE TABLE test6b OF priv_testtype1;
CREATE TABLE test10b (a int[], b priv_testtype1[]);
CREATE TABLE test9b (a int, b int);
ALTER TABLE test9b ADD COLUMN c priv_testdomain1;
ALTER TABLE test9b ALTER COLUMN b TYPE priv_testdomain1;
CREATE TYPE test7b AS (a int, b priv_testdomain1);
CREATE TYPE test8b AS (a int, b int);
ALTER TYPE test8b ADD ATTRIBUTE c priv_testdomain1;
ALTER TYPE test8b ALTER ATTRIBUTE b TYPE priv_testdomain1;
CREATE TABLE test11b AS (SELECT 1::priv_testdomain1 AS a);
REVOKE ALL ON TYPE priv_testtype1 FROM PUBLIC;
\c -DROP AGGREGATE priv_testagg1b(priv_testdomain1);
DROP DOMAIN priv_testdomain2b;
DROP OPERATOR !! (NONE, priv_testdomain1);
DROP FUNCTION priv_testfunc5b(a priv_testdomain1);
DROP FUNCTION priv_testfunc6b(b int);
DROP TABLE test5b;
DROP TABLE test6b;
DROP TABLE test9b;
DROP TABLE test10b;
DROP TYPE test7b;
DROP TYPE test8b;
DROP CAST (priv_testdomain1 AS priv_testdomain3b);
DROP FUNCTION castfunc(int) CASCADE;
DROP DOMAIN priv_testdomain3b;
DROP TABLE test11b;
DROP TYPE priv_testtype1;
 DROP DOMAIN priv_testdomain1;
 SET SESSION AUTHORIZATION regress_priv_user5;
 SET SESSION AUTHORIZATION regress_priv_user5;
TRUNCATE atest2;
 TRUNCATE atest3;
 select has_table_privilege(NULL,'pg_authid','select');
 select has_table_privilege(NULL,'pg_authid','select');
select has_table_privilege('pg_shad','select');
select has_table_privilege('nosuchuser','pg_authid','select');
select has_table_privilege('pg_authid','sel');
select has_table_privilege(-999999,'pg_authid','update');
select has_table_privilege(1,'select');
\c -select has_table_privilege(current_user,'pg_authid','select');
select has_table_privilege(current_user,'pg_authid','insert');
select has_table_privilege(t2.oid,'pg_authid','update')from (select oid from pg_roles where rolname = current_user) as t2;
select has_table_privilege(t2.oid,'pg_authid','delete')from (select oid from pg_roles where rolname = current_user) as t2;
select has_table_privilege(current_user,t1.oid,'rule')from (select oid from pg_class where relname = 'pg_authid') as t1;
select has_table_privilege(current_user,t1.oid,'references')from (select oid from pg_class where relname = 'pg_authid') as t1;
select has_table_privilege(t2.oid,t1.oid,'select')from (select oid from pg_class where relname = 'pg_authid') as t1,  (select oid from pg_roles where rolname = current_user) as t2;
select has_table_privilege(t2.oid,t1.oid,'insert')from (select oid from pg_class where relname = 'pg_authid') as t1,  (select oid from pg_roles where rolname = current_user) as t2;
select has_table_privilege('pg_authid','update');
select has_table_privilege('pg_authid','delete');
select has_table_privilege('pg_authid','truncate');
select has_table_privilege(t1.oid,'select')from (select oid from pg_class where relname = 'pg_authid') as t1;
select has_table_privilege(t1.oid,'trigger')from (select oid from pg_class where relname = 'pg_authid') as t1;
SET SESSION AUTHORIZATION regress_priv_user3;
select has_table_privilege(current_user,'pg_class','select');
select has_table_privilege(current_user,'pg_class','insert');
select has_table_privilege(t2.oid,'pg_class','update')from (select oid from pg_roles where rolname = current_user) as t2;
select has_table_privilege(t2.oid,'pg_class','delete')from (select oid from pg_roles where rolname = current_user) as t2;
select has_table_privilege(current_user,t1.oid,'references')from (select oid from pg_class where relname = 'pg_class') as t1;
select has_table_privilege(t2.oid,t1.oid,'select')from (select oid from pg_class where relname = 'pg_class') as t1,  (select oid from pg_roles where rolname = current_user) as t2;
select has_table_privilege(t2.oid,t1.oid,'insert')from (select oid from pg_class where relname = 'pg_class') as t1,  (select oid from pg_roles where rolname = current_user) as t2;
select has_table_privilege('pg_class','update');
select has_table_privilege('pg_class','delete');
select has_table_privilege('pg_class','truncate');
select has_table_privilege(t1.oid,'select')from (select oid from pg_class where relname = 'pg_class') as t1;
select has_table_privilege(t1.oid,'trigger')from (select oid from pg_class where relname = 'pg_class') as t1;
select has_table_privilege(current_user,'atest1','select');
select has_table_privilege(current_user,'atest1','insert');
select has_table_privilege(t2.oid,'atest1','update')from (select oid from pg_roles where rolname = current_user) as t2;
select has_table_privilege(t2.oid,'atest1','delete')from (select oid from pg_roles where rolname = current_user) as t2;
select has_table_privilege(current_user,t1.oid,'references')from (select oid from pg_class where relname = 'atest1') as t1;
select has_table_privilege(t2.oid,t1.oid,'select')from (select oid from pg_class where relname = 'atest1') as t1,  (select oid from pg_roles where rolname = current_user) as t2;
select has_table_privilege(t2.oid,t1.oid,'insert')from (select oid from pg_class where relname = 'atest1') as t1,  (select oid from pg_roles where rolname = current_user) as t2;
select has_table_privilege('atest1','update');
select has_table_privilege('atest1','delete');
select has_table_privilege('atest1','truncate');
select has_table_privilege(t1.oid,'select')from (select oid from pg_class where relname = 'atest1') as t1;
select has_table_privilege(t1.oid,'trigger')from (select oid from pg_class where relname = 'atest1') as t1;
select has_column_privilege('pg_authid',NULL,'select');
select has_column_privilege('pg_authid','nosuchcol','select');
select has_column_privilege(9999,'nosuchcol','select');
select has_column_privilege(9999,99::int2,'select');
select has_column_privilege('pg_authid',99::int2,'select');
select has_column_privilege(9999,99::int2,'select');
create temp table mytable(f1 int, f2 int, f3 int);
alter table mytable drop column f2;
select has_column_privilege('mytable','f2','select');
select has_column_privilege('mytable','........pg.dropped.2........','select');
select has_column_privilege('mytable',2::int2,'select');
revoke select on table mytable from regress_priv_user3;
select has_column_privilege('mytable',2::int2,'select');
drop table mytable;
SET SESSION AUTHORIZATION regress_priv_user1;
CREATE TABLE atest4 (a int);
GRANT SELECT ON atest4 TO regress_priv_user2 WITH GRANT OPTION;
GRANT UPDATE ON atest4 TO regress_priv_user2;
GRANT SELECT ON atest4 TO GROUP regress_priv_group1 WITH GRANT OPTION;
SET SESSION AUTHORIZATION regress_priv_user2;
GRANT SELECT ON atest4 TO regress_priv_user3;
GRANT UPDATE ON atest4 TO regress_priv_user3;
 SET SESSION AUTHORIZATION regress_priv_user1;
 SET SESSION AUTHORIZATION regress_priv_user1;
REVOKE SELECT ON atest4 FROM regress_priv_user3;
 SELECT has_table_privilege('regress_priv_user3', 'atest4', 'SELECT');
 REVOKE SELECT ON atest4 FROM regress_priv_user2;
 REVOKE GRANT OPTION FOR SELECT ON atest4 FROM regress_priv_user2 CASCADE;
 SELECT has_table_privilege('regress_priv_user2', 'atest4', 'SELECT');
 SELECT has_table_privilege('regress_priv_user3', 'atest4', 'SELECT');
 SELECT has_table_privilege('regress_priv_user1', 'atest4', 'SELECT WITH GRANT OPTION');
 \c -CREATE ROLE regress_sro_user;
 \c -CREATE ROLE regress_sro_user;
SET SESSION AUTHORIZATION regress_sro_user;
CREATE FUNCTION unwanted_grant() RETURNS void LANGUAGE sql AS	'GRANT regress_priv_group2 TO regress_sro_user';
CREATE FUNCTION mv_action() RETURNS bool LANGUAGE sql AS	'DECLARE c CURSOR WITH HOLD FOR SELECT unwanted_grant();
 SELECT true';
 SELECT true';
CREATE MATERIALIZED VIEW sro_mv AS SELECT mv_action() WITH NO DATA;
REFRESH MATERIALIZED VIEW sro_mv;
\c -REFRESH MATERIALIZED VIEW sro_mv;
SET SESSION AUTHORIZATION regress_sro_user;
CREATE TABLE sro_trojan_table ();
CREATE FUNCTION sro_trojan() RETURNS trigger LANGUAGE plpgsql AS	'BEGIN PERFORM unwanted_grant();
 RETURN NULL;
 END';
 END';
CREATE CONSTRAINT TRIGGER t AFTER INSERT ON sro_trojan_table    INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE sro_trojan();
CREATE OR REPLACE FUNCTION mv_action() RETURNS bool LANGUAGE sql AS	'INSERT INTO sro_trojan_table DEFAULT VALUES;
 SELECT true';
 SELECT true';
REFRESH MATERIALIZED VIEW sro_mv;
\c -REFRESH MATERIALIZED VIEW sro_mv;
BEGIN;
 SET CONSTRAINTS ALL IMMEDIATE;
 REFRESH MATERIALIZED VIEW sro_mv;
 COMMIT;
 COMMIT;
DROP OWNED BY regress_sro_user;
DROP ROLE regress_sro_user;
SET SESSION AUTHORIZATION regress_priv_user4;
CREATE FUNCTION dogrant_ok() RETURNS void LANGUAGE sql SECURITY DEFINER AS	'GRANT regress_priv_group2 TO regress_priv_user5';
GRANT regress_priv_group2 TO regress_priv_user5;
 SET ROLE regress_priv_group2;
 SET ROLE regress_priv_group2;
GRANT regress_priv_group2 TO regress_priv_user5;
 SET SESSION AUTHORIZATION regress_priv_user1;
 SET SESSION AUTHORIZATION regress_priv_user1;
GRANT regress_priv_group2 TO regress_priv_user5;
 SELECT dogrant_ok();
			SET ROLE regress_priv_group2;
			SET ROLE regress_priv_group2;
GRANT regress_priv_group2 TO regress_priv_user5;
 SET SESSION AUTHORIZATION regress_priv_group2;
 SET SESSION AUTHORIZATION regress_priv_group2;
GRANT regress_priv_group2 TO regress_priv_user5;
 CREATE FUNCTION dogrant_fails() RETURNS void LANGUAGE sql SECURITY DEFINER AS	'GRANT regress_priv_group2 TO regress_priv_user5';
 CREATE FUNCTION dogrant_fails() RETURNS void LANGUAGE sql SECURITY DEFINER AS	'GRANT regress_priv_group2 TO regress_priv_user5';
SELECT dogrant_fails();
			DROP FUNCTION dogrant_fails();
			DROP FUNCTION dogrant_fails();
SET SESSION AUTHORIZATION regress_priv_user4;
DROP FUNCTION dogrant_ok();
REVOKE regress_priv_group2 FROM regress_priv_user5;
\c -CREATE SEQUENCE x_seq;
GRANT USAGE on x_seq to regress_priv_user2;
SELECT has_sequence_privilege('regress_priv_user1', 'atest1', 'SELECT');
SELECT has_sequence_privilege('regress_priv_user1', 'x_seq', 'INSERT');
SELECT has_sequence_privilege('regress_priv_user1', 'x_seq', 'SELECT');
SET SESSION AUTHORIZATION regress_priv_user2;
SELECT has_sequence_privilege('x_seq', 'USAGE');
\c -SET SESSION AUTHORIZATION regress_priv_user1;
SELECT lo_create(1001);
SELECT lo_create(1002);
SELECT lo_create(1003);
SELECT lo_create(1004);
SELECT lo_create(1005);
GRANT ALL ON LARGE OBJECT 1001 TO PUBLIC;
GRANT SELECT ON LARGE OBJECT 1003 TO regress_priv_user2;
GRANT SELECT,UPDATE ON LARGE OBJECT 1004 TO regress_priv_user2;
GRANT ALL ON LARGE OBJECT 1005 TO regress_priv_user2;
GRANT SELECT ON LARGE OBJECT 1005 TO regress_priv_user2 WITH GRANT OPTION;
GRANT SELECT, INSERT ON LARGE OBJECT 1001 TO PUBLIC;
	GRANT SELECT, UPDATE ON LARGE OBJECT 1001 TO nosuchuser;
	GRANT SELECT, UPDATE ON LARGE OBJECT  999 TO PUBLIC;
	\c -SET SESSION AUTHORIZATION regress_priv_user2;
	\c -SET SESSION AUTHORIZATION regress_priv_user2;
SELECT lo_create(2001);
SELECT lo_create(2002);
SELECT loread(lo_open(1001, x'20000'::int), 32);
	SELECT lowrite(lo_open(1001, x'40000'::int), 'abcd');
	SELECT loread(lo_open(1001, x'40000'::int), 32);
	SELECT loread(lo_open(1001, x'40000'::int), 32);
SELECT loread(lo_open(1002, x'40000'::int), 32);
	SELECT loread(lo_open(1003, x'40000'::int), 32);
	SELECT loread(lo_open(1003, x'40000'::int), 32);
SELECT loread(lo_open(1004, x'40000'::int), 32);
SELECT lowrite(lo_open(1001, x'20000'::int), 'abcd');
SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd');
	SELECT lowrite(lo_open(1003, x'20000'::int), 'abcd');
	SELECT lowrite(lo_open(1004, x'20000'::int), 'abcd');
	SELECT lowrite(lo_open(1004, x'20000'::int), 'abcd');
GRANT SELECT ON LARGE OBJECT 1005 TO regress_priv_user3;
GRANT UPDATE ON LARGE OBJECT 1006 TO regress_priv_user3;
	REVOKE ALL ON LARGE OBJECT 2001, 2002 FROM PUBLIC;
	REVOKE ALL ON LARGE OBJECT 2001, 2002 FROM PUBLIC;
GRANT ALL ON LARGE OBJECT 2001 TO regress_priv_user3;
SELECT lo_unlink(1001);
		SELECT lo_unlink(2002);
		SELECT lo_unlink(2002);
\c -SELECT oid, pg_get_userbyid(lomowner) ownername, lomacl FROM pg_largeobject_metadata WHERE oid >= 1000 AND oid < 3000 ORDER BY oid;
SET SESSION AUTHORIZATION regress_priv_user3;
SELECT loread(lo_open(1001, x'40000'::int), 32);
SELECT loread(lo_open(1003, x'40000'::int), 32);
	SELECT loread(lo_open(1005, x'40000'::int), 32);
	SELECT loread(lo_open(1005, x'40000'::int), 32);
SELECT lo_truncate(lo_open(1005, x'20000'::int), 10);
	SELECT lo_truncate(lo_open(2001, x'20000'::int), 10);
	SELECT lo_truncate(lo_open(2001, x'20000'::int), 10);
\c -SET lo_compat_privileges = false;
	SET SESSION AUTHORIZATION regress_priv_user4;
	SET SESSION AUTHORIZATION regress_priv_user4;
SELECT loread(lo_open(1002, x'40000'::int), 32);
	SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd');
	SELECT lo_truncate(lo_open(1002, x'20000'::int), 10);
	SELECT lo_put(1002, 1, 'abcd');
				SELECT lo_unlink(1002);
					SELECT lo_export(1001, '/dev/null');
			SELECT lo_import('/dev/null');
				SELECT lo_import('/dev/null', 2003);
			\c -SET lo_compat_privileges = true;
	SET SESSION AUTHORIZATION regress_priv_user4;
	SET SESSION AUTHORIZATION regress_priv_user4;
SELECT loread(lo_open(1002, x'40000'::int), 32);
SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd');
SELECT lo_truncate(lo_open(1002, x'20000'::int), 10);
SELECT lo_unlink(1002);
SELECT lo_export(1001, '/dev/null');
			\c -SELECT * FROM pg_largeobject LIMIT 0;
			\c -SELECT * FROM pg_largeobject LIMIT 0;
SET SESSION AUTHORIZATION regress_priv_user1;
SELECT * FROM pg_largeobject LIMIT 0;
			\c -CREATE SCHEMA testns;
			\c -CREATE SCHEMA testns;
GRANT ALL ON SCHEMA testns TO regress_priv_user1;
CREATE TABLE testns.acltest1 (x int);
SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT');
 SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT');
 ALTER DEFAULT PRIVILEGES IN SCHEMA testns,testns GRANT SELECT ON TABLES TO public,public;
 ALTER DEFAULT PRIVILEGES IN SCHEMA testns,testns GRANT SELECT ON TABLES TO public,public;
SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT');
 SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT');
 DROP TABLE testns.acltest1;
 DROP TABLE testns.acltest1;
CREATE TABLE testns.acltest1 (x int);
SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT');
 SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT');
 ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT INSERT ON TABLES TO regress_priv_user1;
 ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT INSERT ON TABLES TO regress_priv_user1;
DROP TABLE testns.acltest1;
CREATE TABLE testns.acltest1 (x int);
SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT');
 SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT');
 ALTER DEFAULT PRIVILEGES IN SCHEMA testns REVOKE INSERT ON TABLES FROM regress_priv_user1;
 ALTER DEFAULT PRIVILEGES IN SCHEMA testns REVOKE INSERT ON TABLES FROM regress_priv_user1;
DROP TABLE testns.acltest1;
CREATE TABLE testns.acltest1 (x int);
SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT');
 SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT');
 ALTER DEFAULT PRIVILEGES FOR ROLE regress_priv_user1 REVOKE EXECUTE ON FUNCTIONS FROM public;
 ALTER DEFAULT PRIVILEGES FOR ROLE regress_priv_user1 REVOKE EXECUTE ON FUNCTIONS FROM public;
ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT USAGE ON SCHEMAS TO regress_priv_user2;
 BEGIN;
 BEGIN;
ALTER DEFAULT PRIVILEGES GRANT USAGE ON SCHEMAS TO regress_priv_user2;
CREATE SCHEMA testns2;
SELECT has_schema_privilege('regress_priv_user2', 'testns2', 'USAGE');
 SELECT has_schema_privilege('regress_priv_user2', 'testns2', 'CREATE');
 ALTER DEFAULT PRIVILEGES REVOKE USAGE ON SCHEMAS FROM regress_priv_user2;
 ALTER DEFAULT PRIVILEGES REVOKE USAGE ON SCHEMAS FROM regress_priv_user2;
CREATE SCHEMA testns3;
SELECT has_schema_privilege('regress_priv_user2', 'testns3', 'USAGE');
 SELECT has_schema_privilege('regress_priv_user2', 'testns3', 'CREATE');
 ALTER DEFAULT PRIVILEGES GRANT ALL ON SCHEMAS TO regress_priv_user2;
 ALTER DEFAULT PRIVILEGES GRANT ALL ON SCHEMAS TO regress_priv_user2;
CREATE SCHEMA testns4;
SELECT has_schema_privilege('regress_priv_user2', 'testns4', 'USAGE');
 SELECT has_schema_privilege('regress_priv_user2', 'testns4', 'CREATE');
 ALTER DEFAULT PRIVILEGES REVOKE ALL ON SCHEMAS FROM regress_priv_user2;
 ALTER DEFAULT PRIVILEGES REVOKE ALL ON SCHEMAS FROM regress_priv_user2;
COMMIT;
CREATE SCHEMA testns5;
SELECT has_schema_privilege('regress_priv_user2', 'testns5', 'USAGE');
 SELECT has_schema_privilege('regress_priv_user2', 'testns5', 'CREATE');
 SET ROLE regress_priv_user1;
 SET ROLE regress_priv_user1;
CREATE FUNCTION testns.foo() RETURNS int AS 'select 1' LANGUAGE sql;
CREATE AGGREGATE testns.agg1(int) (sfunc = int4pl, stype = int4);
CREATE PROCEDURE testns.bar() AS 'select 1' LANGUAGE sql;
SELECT has_function_privilege('regress_priv_user2', 'testns.foo()', 'EXECUTE');
 SELECT has_function_privilege('regress_priv_user2', 'testns.agg1(int)', 'EXECUTE');
 SELECT has_function_privilege('regress_priv_user2', 'testns.bar()', 'EXECUTE');
 ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT EXECUTE ON ROUTINES to public;
 ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT EXECUTE ON ROUTINES to public;
DROP FUNCTION testns.foo();
CREATE FUNCTION testns.foo() RETURNS int AS 'select 1' LANGUAGE sql;
DROP AGGREGATE testns.agg1(int);
CREATE AGGREGATE testns.agg1(int) (sfunc = int4pl, stype = int4);
DROP PROCEDURE testns.bar();
CREATE PROCEDURE testns.bar() AS 'select 1' LANGUAGE sql;
SELECT has_function_privilege('regress_priv_user2', 'testns.foo()', 'EXECUTE');
 SELECT has_function_privilege('regress_priv_user2', 'testns.agg1(int)', 'EXECUTE');
 SELECT has_function_privilege('regress_priv_user2', 'testns.bar()', 'EXECUTE');
 DROP FUNCTION testns.foo();
 DROP FUNCTION testns.foo();
DROP AGGREGATE testns.agg1(int);
DROP PROCEDURE testns.bar();
ALTER DEFAULT PRIVILEGES FOR ROLE regress_priv_user1 REVOKE USAGE ON TYPES FROM public;
CREATE DOMAIN testns.priv_testdomain1 AS int;
SELECT has_type_privilege('regress_priv_user2', 'testns.priv_testdomain1', 'USAGE');
 ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT USAGE ON TYPES to public;
 ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT USAGE ON TYPES to public;
DROP DOMAIN testns.priv_testdomain1;
CREATE DOMAIN testns.priv_testdomain1 AS int;
SELECT has_type_privilege('regress_priv_user2', 'testns.priv_testdomain1', 'USAGE');
 DROP DOMAIN testns.priv_testdomain1;
 DROP DOMAIN testns.priv_testdomain1;
RESET ROLE;
SELECT count(*)  FROM pg_default_acl d LEFT JOIN pg_namespace n ON defaclnamespace = n.oid  WHERE nspname = 'testns';
DROP SCHEMA testns CASCADE;
DROP SCHEMA testns2 CASCADE;
DROP SCHEMA testns3 CASCADE;
DROP SCHEMA testns4 CASCADE;
DROP SCHEMA testns5 CASCADE;
SELECT d.*       FROM pg_default_acl d LEFT JOIN pg_namespace n ON defaclnamespace = n.oid  WHERE nspname IS NULL AND defaclnamespace != 0;
\c -CREATE SCHEMA testns;
CREATE TABLE testns.t1 (f1 int);
CREATE TABLE testns.t2 (f1 int);
SELECT has_table_privilege('regress_priv_user1', 'testns.t1', 'SELECT');
 GRANT ALL ON ALL TABLES IN SCHEMA testns TO regress_priv_user1;
 GRANT ALL ON ALL TABLES IN SCHEMA testns TO regress_priv_user1;
SELECT has_table_privilege('regress_priv_user1', 'testns.t1', 'SELECT');
 SELECT has_table_privilege('regress_priv_user1', 'testns.t2', 'SELECT');
 REVOKE ALL ON ALL TABLES IN SCHEMA testns FROM regress_priv_user1;
 REVOKE ALL ON ALL TABLES IN SCHEMA testns FROM regress_priv_user1;
SELECT has_table_privilege('regress_priv_user1', 'testns.t1', 'SELECT');
 SELECT has_table_privilege('regress_priv_user1', 'testns.t2', 'SELECT');
 CREATE FUNCTION testns.priv_testfunc(int) RETURNS int AS 'select 3 *  1;
 CREATE FUNCTION testns.priv_testfunc(int) RETURNS int AS 'select 3 *  1;
' LANGUAGE sql;
' LANGUAGE sql;
CREATE AGGREGATE testns.priv_testagg(int) (sfunc = int4pl, stype = int4);
CREATE PROCEDURE testns.priv_testproc(int) AS 'select 3' LANGUAGE sql;
SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testfunc(int)', 'EXECUTE');
 SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testagg(int)', 'EXECUTE');
 SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testproc(int)', 'EXECUTE');
 REVOKE ALL ON ALL FUNCTIONS IN SCHEMA testns FROM PUBLIC;
 REVOKE ALL ON ALL FUNCTIONS IN SCHEMA testns FROM PUBLIC;
SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testfunc(int)', 'EXECUTE');
 SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testagg(int)', 'EXECUTE');
 SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testproc(int)', 'EXECUTE');
 REVOKE ALL ON ALL PROCEDURES IN SCHEMA testns FROM PUBLIC;
 REVOKE ALL ON ALL PROCEDURES IN SCHEMA testns FROM PUBLIC;
SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testproc(int)', 'EXECUTE');
 GRANT ALL ON ALL ROUTINES IN SCHEMA testns TO PUBLIC;
 GRANT ALL ON ALL ROUTINES IN SCHEMA testns TO PUBLIC;
SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testfunc(int)', 'EXECUTE');
 SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testagg(int)', 'EXECUTE');
 SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testproc(int)', 'EXECUTE');
 DROP SCHEMA testns CASCADE;
 DROP SCHEMA testns CASCADE;
\c -CREATE ROLE regress_schemauser1 superuser login;
CREATE ROLE regress_schemauser2 superuser login;
SET SESSION ROLE regress_schemauser1;
CREATE SCHEMA testns;
SELECT nspname, rolname FROM pg_namespace, pg_roles WHERE pg_namespace.nspname = 'testns' AND pg_namespace.nspowner = pg_roles.oid;
ALTER SCHEMA testns OWNER TO regress_schemauser2;
ALTER ROLE regress_schemauser2 RENAME TO regress_schemauser_renamed;
SELECT nspname, rolname FROM pg_namespace, pg_roles WHERE pg_namespace.nspname = 'testns' AND pg_namespace.nspowner = pg_roles.oid;
set session role regress_schemauser_renamed;
DROP SCHEMA testns CASCADE;
\c -DROP ROLE regress_schemauser1;
DROP ROLE regress_schemauser_renamed;
\c -set session role regress_priv_user1;
create table dep_priv_test (a int);
grant select on dep_priv_test to regress_priv_user2 with grant option;
grant select on dep_priv_test to regress_priv_user3 with grant option;
set session role regress_priv_user2;
grant select on dep_priv_test to regress_priv_user4 with grant option;
set session role regress_priv_user3;
grant select on dep_priv_test to regress_priv_user4 with grant option;
set session role regress_priv_user4;
grant select on dep_priv_test to regress_priv_user5;
\dp dep_priv_testset session role regress_priv_user2;
revoke select on dep_priv_test from regress_priv_user4 cascade;
\dp dep_priv_testset session role regress_priv_user3;
revoke select on dep_priv_test from regress_priv_user4 cascade;
\dp dep_priv_testset session role regress_priv_user1;
drop table dep_priv_test;
\cdrop sequence x_seq;
DROP AGGREGATE priv_testagg1(int);
DROP FUNCTION priv_testfunc2(int);
DROP FUNCTION priv_testfunc4(boolean);
DROP PROCEDURE priv_testproc1(int);
DROP VIEW atestv0;
DROP VIEW atestv1;
DROP VIEW atestv2;
DROP VIEW atestv3 CASCADE;
DROP VIEW atestv4;
DROP TABLE atest1;
DROP TABLE atest2;
DROP TABLE atest3;
DROP TABLE atest4;
DROP TABLE atest5;
DROP TABLE atest6;
DROP TABLE atestc;
DROP TABLE atestp1;
DROP TABLE atestp2;
SELECT lo_unlink(oid) FROM pg_largeobject_metadata WHERE oid >= 1000 AND oid < 3000 ORDER BY oid;
DROP GROUP regress_priv_group1;
DROP GROUP regress_priv_group2;
REVOKE USAGE ON LANGUAGE sql FROM regress_priv_user1;
DROP OWNED BY regress_priv_user1;
DROP USER regress_priv_user1;
DROP USER regress_priv_user2;
DROP USER regress_priv_user3;
DROP USER regress_priv_user4;
DROP USER regress_priv_user5;
DROP USER regress_priv_user6;
CREATE USER regress_locktable_user;
CREATE TABLE lock_table (a int);
GRANT SELECT ON lock_table TO regress_locktable_user;
SET SESSION AUTHORIZATION regress_locktable_user;
BEGIN;
LOCK TABLE lock_table IN ROW EXCLUSIVE MODE;
 ROLLBACK;
 ROLLBACK;
BEGIN;
LOCK TABLE lock_table IN ACCESS SHARE MODE;
 COMMIT;
 COMMIT;
BEGIN;
LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE;
 ROLLBACK;
 ROLLBACK;
\cREVOKE SELECT ON lock_table FROM regress_locktable_user;
GRANT INSERT ON lock_table TO regress_locktable_user;
SET SESSION AUTHORIZATION regress_locktable_user;
BEGIN;
LOCK TABLE lock_table IN ROW EXCLUSIVE MODE;
 COMMIT;
 COMMIT;
BEGIN;
LOCK TABLE lock_table IN ACCESS SHARE MODE;
 ROLLBACK;
 ROLLBACK;
BEGIN;
LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE;
 ROLLBACK;
 ROLLBACK;
\cREVOKE INSERT ON lock_table FROM regress_locktable_user;
GRANT UPDATE ON lock_table TO regress_locktable_user;
SET SESSION AUTHORIZATION regress_locktable_user;
BEGIN;
LOCK TABLE lock_table IN ROW EXCLUSIVE MODE;
 COMMIT;
 COMMIT;
BEGIN;
LOCK TABLE lock_table IN ACCESS SHARE MODE;
 ROLLBACK;
 ROLLBACK;
BEGIN;
LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE;
 COMMIT;
 COMMIT;
\cREVOKE UPDATE ON lock_table FROM regress_locktable_user;
GRANT DELETE ON lock_table TO regress_locktable_user;
SET SESSION AUTHORIZATION regress_locktable_user;
BEGIN;
LOCK TABLE lock_table IN ROW EXCLUSIVE MODE;
 COMMIT;
 COMMIT;
BEGIN;
LOCK TABLE lock_table IN ACCESS SHARE MODE;
 ROLLBACK;
 ROLLBACK;
BEGIN;
LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE;
 COMMIT;
 COMMIT;
\cREVOKE DELETE ON lock_table FROM regress_locktable_user;
GRANT TRUNCATE ON lock_table TO regress_locktable_user;
SET SESSION AUTHORIZATION regress_locktable_user;
BEGIN;
LOCK TABLE lock_table IN ROW EXCLUSIVE MODE;
 COMMIT;
 COMMIT;
BEGIN;
LOCK TABLE lock_table IN ACCESS SHARE MODE;
 ROLLBACK;
 ROLLBACK;
BEGIN;
LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE;
 COMMIT;
 COMMIT;
\cREVOKE TRUNCATE ON lock_table FROM regress_locktable_user;
DROP TABLE lock_table;
DROP USER regress_locktable_user;
